 
ALTER         proc sp_create_update_test_lot_stage (
 
 @part_number varchar(50), 
 @lot_number varchar(50), 
 @date_in datetime,
 @date_out datetime,
 @qty_in int,
 @qty_out int,
 @expected_date_out datetime,
 @lot_stage_name varchar(50),
 @lot_stage_position varchar(50),
 @vendor_name varchar(50),
 @hold_code varchar(50),
 @hold_reason varchar(50),
 @ps_type varchar (50),
 @revised_date_out datetime)
 as
 begin
 	declare @process_step_id int
 	declare @location_id int
 	declare @int int
 	declare @normal_cost float
 	declare @lot_id int
 	declare @this_lot_stauts varchar(50)
 
 	declare @stage_id int
 	declare @test_lot_id int
 	
 	
 	declare @date_time datetime
 	declare @start_date datetime
 	declare @end_date datetime
 
 
 	if @ps_type is null
        begin
 		select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name like 'Test' and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
 
 		if @process_step_id is null
 		begin
 			select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number like @part_number  and PS_Type_Name like 'Test' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID 
 
 			if @process_step_id is null
 			begin
				--commented by chandrasekhar
 				--exec sp_create_update_miscellaneous_lot @part_number, @lot_number, null, null, null,
 				--@vendor_name, 'TEST', 'Test', null, @date_in, @date_out, @expected_date_out, null, null, null, @qty_in, @qty_out, null, null, 'Running'
 
 				--updating the hold reason and hold code
 				update Miscellaneous_Lot_Table set Hold_Code = @hold_code, Hold_Reason = @hold_reason where Part_Number = @part_number and Lot_Number = @lot_number and Vendor_Name = @vendor_name and PS_Type_Name = 'Test' and TimeStamp between convert(datetime, convert(varchar, current_timestamp, 101)+' 12:00AM' ) and convert(datetime, convert(varchar, current_timestamp, 101)+' 11:59PM')				
 
 		       		-- dummy update to avoid SQLException
 		       		update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
 		       		return
 
 			end
 
 		end
         end
 	 else
         begin
		select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type = @ps_type and PS_Type_Name like 'Test' and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
 
 		if @process_step_id is null
 		begin
 			select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number like @part_number  and PS_Type = @ps_type and PS_Type_Name like 'Test' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID 
 
 			if @process_step_id is null
 			begin
				--commented by chandrasekhar
 				--exec sp_create_update_miscellaneous_lot @part_number, @lot_number, null, null, null,
 				--@vendor_name, 'TEST', 'Test', null, @date_in, @date_out, @expected_date_out, null, null, null, @qty_in, @qty_out, null, null, 'Running'
 
 				--updating the hold reason and hold code
 				update Miscellaneous_Lot_Table set Hold_Code = @hold_code, Hold_Reason = @hold_reason where Part_Number = @part_number and Lot_Number = @lot_number and Vendor_Name = @vendor_name and PS_Type_Name = 'Test' and TimeStamp between convert(datetime, convert(varchar, current_timestamp, 101)+' 12:00AM' ) and convert(datetime, convert(varchar, current_timestamp, 101)+' 11:59PM')				
 
 		       		-- dummy update to avoid SQLException
 		       		update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
 		       		return
 
 			end
 
 		end
         end

 
 	if @process_step_id is not null
 	begin
 		update Miscellaneous_Lot_Table set Status = 'Close' where Part_Number = @part_number and Lot_Number = @lot_number and Vendor_Name = @vendor_name
 	end
	 
 	select @location_id = Location_ID from Location where L_Type = 'Test' and L_Vendor_Name = @vendor_name
 
 	select @this_lot_stauts = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 	        
 	-- Get Stage ID
 	select @stage_id = Stage_ID from Test_Stage_Table where TST_Stage_Name = @lot_stage_name
 	if @stage_id is null
 	begin
 		insert into Test_Stage_Table (ProcessStep_ID, TST_Stage_Name, Timestamp) values (@process_step_id, @lot_stage_name, CURRENT_TIMESTAMP)
		select @stage_id = @@IDENTITY
		/* to send aletr whenever new stage is created in Test process */
		--insert into New_Fab_Stage_Id_Table ( Fab_Stage_Id, Fab_Stage_Name, part_number , Timestamp, Process_Type ) values (@stage_id, @lot_stage_name, @part_number , CURRENT_TIMESTAMP, 'TEST' )
 
 	end
 	select @stage_id = Stage_ID from Test_Stage_Table where TST_Stage_Name = @lot_stage_name
 
 	declare @temp_varchar varchar(20)
 	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
 	set @start_date = convert(datetime, @temp_varchar)
 	set @temp_varchar = null
 	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
 	set @end_date = convert(datetime, @temp_varchar)
 	 
 	select @test_lot_id = Lot_ID, @date_time = Timestamp from Test_Stage_Lot where Lot_ID = @lot_id and Stage_ID=@stage_id and timestamp between @start_date and @end_date
 	if @test_lot_id is null 
 	begin
 		insert into Test_Stage_Lot (Lot_ID, Stage_ID, TST_Stage_Position, TST_Qty_In, TST_Date_In,Expected_Date_Out, Timestamp,Hold_Code, Hold_Reason, TST_Revised_Date_Out ) values (@lot_id, @stage_id, @lot_stage_position, @qty_in, @date_in, @expected_date_out, CURRENT_TIMESTAMP,@hold_code, @hold_reason, @revised_date_out)  	
	end
 	else
 	begin
 	     if @qty_out is null
 	     begin
 		if @qty_in is not null and @qty_in <> 0
 		begin
 			
 			if @date_time is not null
 				begin
 					if @date_time between @start_date and @end_date
 					begin
 						update Test_Stage_Lot set Stage_ID=@stage_id, TST_Stage_Position=@lot_stage_position, TST_Qty_In=@qty_in, TST_Date_In=@date_in, Expected_Date_Out=@expected_date_out, Timestamp=CURRENT_TIMESTAMP,Hold_Code=@hold_code, Hold_Reason=@hold_reason where Lot_ID = @test_lot_id and Stage_ID=@stage_id and timestamp between @start_date and @end_date
 					end
 					/*else
 					begin
 						--commented by chandrasekhar
 						--delete from Test_Stage_Lot where Lot_ID = @lot_id and Stage_ID=@stage_id
 						--insert into Test_Stage_Lot (Lot_ID, Stage_ID, TST_Stage_Position, TST_Qty_In, TST_Date_In,Expected_Date_Out, Timestamp,Hold_Code, Hold_Reason) values (@lot_id, @stage_id, @lot_stage_position, @qty_in, @date_in, @expected_date_out, CURRENT_TIMESTAMP,@hold_code, @hold_reason)
 					end*/
 				end
 		end
 		set @date_time = null
 	     end
 	end
 end
